package com.ddyx.common;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
/**
 * <p>Title: ExcelUtils</p> 
 * <p>Description: 
 *			Excel工具类
 * </p>  
 * @author: JiangShuai 
 * @date:   2018年2月6日 下午2:05:12
 * @Copyright: 2018 dingdian.parllay.cn Inc. All rights reserved. 
 * <p>注意：本内容仅限于叮当一下(北京)信息技术有限公司内部传阅，禁止外泄以及用于其他的商业目 </p>
 */
public class ExcelUtils {
	
	/*
	 * 以下是使用org.apache.poi包操作excel
	 */
	
	/**
	 * <p>Title: getExcel</p>
	 * <p>Description: 
	 * 		poi导出excel(浏览器直接输出流)
	 * </p>
	 * @param response
	 * @param titlelist
	 * 			列名集合
	 * @param contentlist
	 * 			内容集合
	 * @param excelname
	 * 			excel名字
	 * @throws Exception
	 */
	public static void getExcel(HttpServletResponse response, List<String> titlelist,
			List<List<String>> contentlist,String excelname) throws Exception{
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        //通过Response把数据以Excel格式保存  
		response.reset();  
		response.setContentType("application/msexcel;charset=UTF-8");  
		response.addHeader("Content-Disposition", "attachment;filename=\""
					+new String((excelname+"-"+date+ ".xls").getBytes("GBK"),"ISO8859_1") + "\"");  
		OutputStream out = response.getOutputStream();  
		getExcel(titlelist, contentlist, out);
	}
	
	/**
	 * <p>Title: getExcel</p>
	 * <p>Description: 
	 * 		poi导出excel(保存到本地)
	 * </p>
	 * @param titlelist
	 * 			列名集合
	 * @param contentlist
	 * 			内容集合
	 * @param excelPath
	 * 			本地excel的保存路径
	 * @throws Exception
	 */
	public static void getExcel(List<String> titlelist,
			List<List<String>> contentlist,String excelPath) throws Exception{
		OutputStream out = new FileOutputStream(excelPath);
		getExcel(titlelist, contentlist, out);
	}
	
	public static void mkdirs(String fileLocalPath) {
        File file = new File(fileLocalPath);
        //file.isDirectory()是一个目录则返回true
        //file.exists() 检查文件是否存在
        if (!file.exists() && !file.isDirectory()) {
            file.mkdirs();
        }
    }
	
	/**
	 * <p>Title: getExcel</p>
	 * <p>Description: 
	 * 		poi导出excel
	 * </p>
	 * @param titlelist
	 * 			列名集合
	 * @param contentlist
	 * 			内容集合
	 * @param out
	 * 			输出流
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public static void getExcel(List<String> titlelist,
			List<List<String>> contentlist,OutputStream out) throws Exception{
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook workBook = new HSSFWorkbook();  
	    // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
	 	HSSFSheet sheet = workBook.createSheet(date);  
	 	// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
	 	HSSFRow row = sheet.createRow((int) 0);
	 	// 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = workBook.createCellStyle();
        style.setWrapText(true);//先设置为自动换行
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直方向上居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 合并单元格   起始行, 终止行, 起始列, 终止列  
        /*CellRangeAddress cra =new CellRangeAddress(1, 3, 1, 3);
        sheet.addMergedRegion(cra);  */
	    //设置第一行为Header
        HSSFCell cell;
        if(null!=titlelist && titlelist.size()>0){
        	for (int i = 0; i < titlelist.size(); i++) {
        		String title=titlelist.get(i);
                cell = row.createCell(i);  
                cell.setCellValue(title);
                cell.setCellStyle(style);
			}
        }
        if(null!=contentlist && contentlist.size()>0){
        	for (int i = 0; i < contentlist.size(); i++){  
        		row = sheet.createRow((int) i + 1);
                //设置行的高度
                row.setHeightInPoints(13);
        		List<String> list=contentlist.get(i);
            	//设置列的宽度
        		for (int j = 0; j < list.size(); j++) {
        			sheet.setColumnWidth(j,4410);
				}
                // 第四步，创建单元格，并设置值  
                HSSFCell cellz;
                for (int h = 0; h < list.size(); h++) {
                	cellz = row.createCell(h);
                    cellz.setCellValue(list.get(h));
                    cellz.setCellStyle(style);
				}
            } 
        }
		try {   
			workBook.write(out);  
			out.flush();  
			out.close();  
		} catch (Exception e) {  
			e.printStackTrace();  
		}
	}
	
	public static void getExcelEveryDay(List<String> titlelist,
			List<List<String>> contentlist,String excelPath) throws Exception{
		OutputStream out = new FileOutputStream(excelPath);
		getExcelEveryDay(titlelist, contentlist, out);
	}
	
	/**
	 * <p>Title: getExcelEveryDay</p>
	 * <p>Description: 
	 * 		每日统计报表-excel格式
	 * </p>
	 * @param titlelist
	 * @param contentlist
	 * @param out
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public static void getExcelEveryDay(List<String> titlelist,
			List<List<String>> contentlist,OutputStream out) throws Exception{
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook workBook = new HSSFWorkbook();  
	    // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
	 	HSSFSheet sheet = workBook.createSheet(date);  
	 	// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
	 	HSSFRow row = sheet.createRow((int) 0);
	 	// 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = workBook.createCellStyle();
        style.setWrapText(true);//先设置为自动换行
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直方向上居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 合并单元格   起始行, 终止行, 起始列, 终止列  
        CellRangeAddress cra1 =new CellRangeAddress(1, 3, 0, 0);
        sheet.addMergedRegion(cra1);
        CellRangeAddress cra2 =new CellRangeAddress(1, 3, 1, 1);
        sheet.addMergedRegion(cra2);
        CellRangeAddress cra3 =new CellRangeAddress(1, 3, 2, 2);
        sheet.addMergedRegion(cra3);
        CellRangeAddress cra4 =new CellRangeAddress(1, 3, 3, 3);
        sheet.addMergedRegion(cra4);
        CellRangeAddress cra5 =new CellRangeAddress(1, 3, 4, 4);
        sheet.addMergedRegion(cra5);
        CellRangeAddress cra6 =new CellRangeAddress(1, 3, 5, 5);
        sheet.addMergedRegion(cra6);
        CellRangeAddress cra7 =new CellRangeAddress(1, 3, 6, 6);
        sheet.addMergedRegion(cra7);
        CellRangeAddress cra8 =new CellRangeAddress(1, 3, 7, 7);
        sheet.addMergedRegion(cra8);
        CellRangeAddress cra9 =new CellRangeAddress(1, 3, 8, 8);
        sheet.addMergedRegion(cra9);
        CellRangeAddress cra10 =new CellRangeAddress(1, 3, 9, 9);
        sheet.addMergedRegion(cra10);
	    //设置第一行为Header
        HSSFCell cell;
        if(null!=titlelist && titlelist.size()>0){
        	for (int i = 0; i < titlelist.size(); i++) {
        		String title=titlelist.get(i);
                cell = row.createCell(i);  
                cell.setCellValue(title);
                cell.setCellStyle(style);
			}
        }
        if(null!=contentlist && contentlist.size()>0){
        	for (int i = 0; i < contentlist.size(); i++){  
        		row = sheet.createRow((int) i + 1);
                //设置行的高度
                row.setHeightInPoints(13);
        		List<String> list=contentlist.get(i);
            	//设置列的宽度
        		for (int j = 0; j < list.size(); j++) {
        			sheet.setColumnWidth(j,5000);
				}
                // 第四步，创建单元格，并设置值  
                HSSFCell cellz;
                for (int h = 0; h < list.size(); h++) {
                	cellz = row.createCell(h);
                	cellz.setCellValue(list.get(h));
                    cellz.setCellStyle(style);
				}
            } 
        }
		try {   
			workBook.write(out);  
			out.flush();  
			out.close();  
		} catch (Exception e) {  
			e.printStackTrace();  
		}
	}
	
	public static void main(String[] args) throws Exception {
		List<String> titlelist=new ArrayList<>();
		titlelist.add("标题1");
		titlelist.add("标题2");
		titlelist.add("标题3");
		titlelist.add("标题4");
		titlelist.add("标题5");
		titlelist.add("标题6");
		titlelist.add("标题7");
		titlelist.add("标题8");
		titlelist.add("标题9");
		List<List<String>> contentlist=new ArrayList<>();
		List<String> con1=new ArrayList<>();
		con1.add("三叶草");
		con1.add("2018-03-21");
		con1.add("2");
		con1.add("");
		con1.add("3");
		con1.add("12");
		con1.add("20000140");
		con1.add("312");
		con1.add("123");
		contentlist.add(con1);
		List<String> con2=new ArrayList<>();
		con2.add("");
		con2.add("");
		con2.add("");
		con2.add("");
		con2.add("");
		con2.add("");
		con2.add("20000110");
		con2.add("312");
		con2.add("123");
		contentlist.add(con2);
		List<String> con3=new ArrayList<>();
		con3.add("");
		con3.add("");
		con3.add("");
		con3.add("");
		con3.add("");
		con3.add("");
		con3.add("20000127");
		con3.add("312");
		con3.add("123");
		contentlist.add(con3);
		
		getExcelEveryDay(titlelist, contentlist, "D:\\2.xls");
	}
	
}
